package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;
import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2PZqfdDao;
import net.sf.json.JSONObject;

/*
 * 
 * @ClassName: P2PZqfdDaoImpl
 * @Description: 债权分档的持久层dao实现类
 * @author: 邵jing
 * @date: 2016年2月19日 下午5:50:53
 */
@Repository
public class P2PZqfdDaoImpl extends P2PJdbcDao implements P2PZqfdDao {

	@Override
	public List queryZqfdDetails(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		String DETAILS_NAME = parms.optString("DETAILS_NAME");
		String CREATE_DATE_START = parms.optString("CREATE_DATE_START");
		String CREATE_DATE_END = parms.optString("CREATE_DATE_END");
		String CREATE_USER_ID = parms.optString("CREATE_USER_ID");
		String DETAILS_NHL_START = parms.optString("DETAILS_NHL_START");
		String DETAILS_NHL_END = parms.optString("DETAILS_NHL_END");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		sql.append(
				" select t.ID,t.DETAILS_NAME,t.DETAILS_NHL,t.DETAILS_MIN_JE,t.DETAILS_MAX_JE,t.DETAILS_CONTENT,t.CREATE_USER_ID,DATE_FORMAT(t.CREATE_DATE,'%Y-%m-%d %T') as CREATE_DATE from t_zhfw_zqfd_details t ");
		sql.append(" where 1=1  ");
		if (StringUtils.isNotEmpty(DETAILS_NAME)) {
			sql.append(" t.DETAILS_NAME like '%" + DETAILS_NAME + "%'");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_START)) {
			sql.append(" t.CREATE_DATE >= :CREATE_DATE_START ");
			parmsMap.put("CREATE_DATE_START", CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_END)) {
			sql.append(" t.CREATE_DATE <= :CREATE_DATE_END ");
			parmsMap.put("CREATE_DATE_END", CREATE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" t.CREATE_USER_ID like :CREATE_USER_ID ");
			parmsMap.put("CREATE_USER_ID", "%" + CREATE_USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" t.DETAILS_NHL >= :DETAILS_NHL_START ");
			parmsMap.put("DETAILS_NHL_START", DETAILS_NHL_START);
		}
		if (StringUtils.isNotEmpty(DETAILS_NHL_END)) {
			sql.append(" t.DETAILS_NHL <= :DETAILS_NHL_END ");
			parmsMap.put("DETAILS_NHL_END", DETAILS_NHL_END);
		}
		sql.append(" order by t.CREATE_DATE desc limit :start,:limit ");
		parmsMap.put("start", start);
		parmsMap.put("limit", limit);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	@Override
	public int queryZqfdDetailsItem(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		String DETAILS_NAME = parms.optString("DETAILS_NAME");
		String CREATE_DATE_START = parms.optString("CREATE_DATE_START");
		String CREATE_DATE_END = parms.optString("CREATE_DATE_END");
		String CREATE_USER_ID = parms.optString("CREATE_USER_ID");
		String DETAILS_NHL_START = parms.optString("DETAILS_NHL_START");
		String DETAILS_NHL_END = parms.optString("DETAILS_NHL_END");
		sql.append(" select count(*) from t_zhfw_zqfd_details t ");
		sql.append(" where 1=1  ");
		if (StringUtils.isNotEmpty(DETAILS_NAME)) {
			sql.append(" t.DETAILS_NAME like '%" + DETAILS_NAME + "%'");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_START)) {
			sql.append(" t.CREATE_DATE >= :CREATE_DATE_START ");
			parmsMap.put("CREATE_DATE_START", CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_END)) {
			sql.append(" t.CREATE_DATE <= :CREATE_DATE_END ");
			parmsMap.put("CREATE_DATE_END", CREATE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" t.CREATE_USER_ID like :CREATE_USER_ID ");
			parmsMap.put("CREATE_USER_ID", "%" + CREATE_USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" t.DETAILS_NHL >= :DETAILS_NHL_START ");
			parmsMap.put("DETAILS_NHL_START", DETAILS_NHL_START);
		}
		if (StringUtils.isNotEmpty(DETAILS_NHL_END)) {
			sql.append(" t.DETAILS_NHL <= :DETAILS_NHL_END ");
			parmsMap.put("DETAILS_NHL_END", DETAILS_NHL_END);
		}
		sql.append(" order by t.CREATE_DATE desc ");
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	@Override
	public List queryZqfd(JSONObject params) {
		String CREATE_DATE_START = params.optString("CREATE_DATE_START");
		String CREATE_DATE_END = params.optString("CREATE_DATE_END");
		String FD_NAME = params.optString("FD_NAME");
		String CREATE_USER_ID = params.optString("CREATE_USER_ID");
		String FD_SEQ = params.optString("FD_SEQ");
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT t.ID,t.FD_NAME,t.FD_SEQ,t.FD_CONTENT,t.CREATE_USER_ID,date_format(t.CREATE_DATE,'%Y-%m-%d %T') AS CREATE_DATE FROM t_zhfw_zqfd t where 1=1 ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		if (StringUtils.isNotEmpty(CREATE_DATE_START)) {
			sql.append(" and t.CREATE_DATE >= :CREATE_DATE_START ");
			paramsMap.put("CREATE_DATE_START", CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_END)) {
			sql.append(" and t.CREATE_DATE <= :CREATE_DATE_END "+" 23:59:59");
			paramsMap.put("CREATE_DATE_END", CREATE_DATE_END);
		}
		if (StringUtils.isNotEmpty(FD_NAME)) {
			sql.append(" and t.FD_NAME like :FD_NAME ");
			paramsMap.put("FD_NAME", "%" + FD_NAME + "%");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" and t.CREATE_USER_ID like :CREATE_USER_ID ");
			paramsMap.put("CREATE_USER_ID", "%" + CREATE_USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(FD_SEQ)) {
			sql.append(" and t.FD_SEQ like :FD_SEQ ");
			paramsMap.put("FD_SEQ", "%" + FD_SEQ + "%");
		}
		sql.append(" order by t.CREATE_DATE desc limit :start,:limit ");
		paramsMap.put("start", start);
		paramsMap.put("limit", limit);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramsMap);
	}

	@Override
	public int queryZqfdItems(JSONObject params) {
		String CREATE_DATE_START = params.optString("CREATE_DATE_START");
		String CREATE_DATE_END = params.optString("CREATE_DATE_END");
		String FD_NAME = params.optString("FD_NAME");
		String CREATE_USER_ID = params.optString("CREATE_USER_ID");
		String FD_SEQ = params.optString("FD_SEQ");
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT count(*) FROM t_zhfw_zqfd t where 1=1 ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		if (StringUtils.isNotEmpty(CREATE_DATE_START)) {
			sql.append(" and t.CREATE_DATE >= :CREATE_DATE_START ");
			paramsMap.put("CREATE_DATE_START", CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(CREATE_DATE_END)) {
			sql.append(" and t.CREATE_DATE <= :CREATE_DATE_END ");
			paramsMap.put("CREATE_DATE_END", CREATE_DATE_END+"23:59:59");
		}
		if (StringUtils.isNotEmpty(FD_NAME)) {
			sql.append(" and t.FD_NAME like :FD_NAME ");
			paramsMap.put("FD_NAME", "%" + FD_NAME + "%");
		}
		if (StringUtils.isNotEmpty(CREATE_USER_ID)) {
			sql.append(" and t.CREATE_USER_ID like :CREATE_USER_ID ");
			paramsMap.put("CREATE_USER_ID", "%" + CREATE_USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(FD_SEQ)) {
			sql.append(" and t.FD_SEQ like :FD_SEQ ");
			paramsMap.put("FD_SEQ", "%" + FD_SEQ + "%");
		}
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramsMap, Integer.class);
	}

	@Override
	public int addZafdDetails(JSONObject params) {
		String DETAILS_NAME = params.optString("DETAILS_NAME");
		String DETAILS_NHL = params.optString("DETAILS_NHL");
		String DETAILS_MIN_JE = params.optString("DETAILS_MIN_JE");
        String DETAILS_MAX_JE = params.optString("DETAILS_MAX_JE");
        String CREATE_USER_ID = params.optString("CREATE_USER_ID");
        String CREATE_DATE = params.optString("CREATE_DATE");
		String DETAILS_CONTENT = params.optString("DETAILS_CONTENT");
		StringBuffer sql  =  new StringBuffer();
		sql.append(" INSERT INTO t_zhfw_zqfd_details (ID, DETAILS_NAME, DETAILS_NHL, DETAILS_MIN_JE, DETAILS_MAX_JE, DETAILS_CONTENT, CREATE_USER_ID, CREATE_DATE) VALUES "+
		" (uuid(),:DETAILS_NAME,:DETAILS_NHL,:DETAILS_MIN_JE,:DETAILS_MAX_JE,:DETAILS_CONTENT,:CREATE_USER_ID,:CREATE_DATE) ");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		paramsMap.put("DETAILS_NAME", DETAILS_NAME);
		paramsMap.put("DETAILS_NHL", DETAILS_NHL);
		paramsMap.put("DETAILS_MIN_JE", DETAILS_MIN_JE);
		paramsMap.put("DETAILS_MAX_JE", DETAILS_MAX_JE);
		paramsMap.put("CREATE_USER_ID", CREATE_USER_ID);
		paramsMap.put("CREATE_DATE", CREATE_DATE);
		paramsMap.put("DETAILS_CONTENT", DETAILS_CONTENT);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	@Override
	public List<JSONObject> checkJE(JSONObject params) {
		String ids = params.optString("ids");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		String[] ss = ids.split(",");
        StringBuffer sql = new StringBuffer();	
        sql.append(" select t.DETAILS_MIN_JE,t.DETAILS_MAX_JE from t_zhfw_zqfd_details t where id in (");
        for(int i = 0;i<ss.length;i++){
        	if(i==ss.length-1){
        		sql.append("'"+ss[i]+"'");
        	}else{
            	sql.append("'"+ss[i]+"'"+",");
        	}
        }
        sql.append(") order by t.DETAILS_MIN_JE ASC ");
        return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramsMap, new ResultSetExtractor<List<JSONObject>>(){
			@Override
			public List<JSONObject> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<JSONObject> list = new ArrayList<>();
				while(rs.next()){
					JSONObject jsonObj = new JSONObject();
					jsonObj.put("DETAILS_MIN_JE", rs.getString("DETAILS_MIN_JE"));
					jsonObj.put("DETAILS_MAX_JE", rs.getString("DETAILS_MAX_JE"));
					list.add(jsonObj);
				}
				return list;
			}
        });
//		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramsMap);
	}

	@Override
	public boolean addZqfd(JSONObject params) {
		String ID = UUID.randomUUID().toString();
		String FD_NAME = params.optString("FD_NAME");
		String FD_SEQ = params.optString("FD_SEQ");
		String FD_CONTENT =params.optString("FD_CONTENT");
		String CREATE_USER_ID = params.optString("CREATE_USER_ID");
		String CREATE_DATE = params.optString("CREATE_DATE");
		String ids = params.optString("detailsId");
		String[] id = ids.split(",");
		StringBuffer sql = new StringBuffer();
		StringBuffer sql01 = new StringBuffer();
		sql.append("INSERT INTO t_zhfw_zqfd (ID, FD_NAME, FD_SEQ, FD_CONTENT, CREATE_USER_ID, CREATE_DATE) VALUES "
		+ " (:ID,:FD_NAME,:FD_SEQ,:FD_CONTENT,:CREATE_USER_ID, :CREATE_DATE) ");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		
		paramsMap.put("ID", ID);
		paramsMap.put("FD_NAME", FD_NAME);
		paramsMap.put("FD_SEQ", FD_SEQ);
		paramsMap.put("FD_CONTENT", FD_CONTENT);
		paramsMap.put("CREATE_USER_ID", CREATE_USER_ID);
		paramsMap.put("CREATE_DATE", CREATE_DATE);
		int i = this.getNamedParameterJdbcTemplate().update(sql.toString(),paramsMap);
		sql01.append("INSERT INTO t_zhfw_zqfd_details_use (ID, ZQFD_ID, ZQFD_DETAILS_ID) VALUES (uuid(),:ID, :the_id) ");
		boolean bool = true;
		for(String the_id:id){
			paramsMap.clear();
			paramsMap.put("the_id",the_id);
			paramsMap.put("ID",ID);
			int j = this.getNamedParameterJdbcTemplate().update(sql01.toString(), paramsMap);
			if(!(j>0)){
				bool = false;
			}
		}
		return i>0&&bool;
	}
}
